﻿Imports System.Data.SqlClient

Public Class cFile

    Dim con As New SqlConnection(strcon)

    'File Contract
    Public Function select_File_Contract(ByVal ContractID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT FileContractID,FileName,UploadDate,UserID FROM tblFileContract WHERE ContractID=N'" & ContractID & "'")
        Return dt
    End Function

    Public Function select_File_Contract_byContractID(ByVal ContractID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT FileContractID,[FileName],FileUpload,UploadDate,UserID FROM tblFileContract WHERE ContractID=N'" & ContractID & "'")
        Return dt
    End Function

    Public Function insert_File_Contract(ByVal ContractID As Long, ByVal FileName As String, ByVal FileUpload As Byte(), ByVal UploadDate As Date, ByVal UserID As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblFileContract(ContractID,FileName,FileUpload,UploadDate,UserID) VALUES(@ContractID,@FileName,@FileUpload,@UploadDate,@UserID)"
        With cmd.Parameters
            .AddWithValue("@ContractID", ContractID)
            .AddWithValue("@FileName", FileName)
            If FileName IsNot Nothing Then
                .AddWithValue("@FileUpload", FileUpload)
            Else
                .Add("@FileUpload", SqlDbType.Image).Value = DBNull.Value
            End If
            .AddWithValue("@UploadDate", UploadDate)
            .AddWithValue("@UserID", UserID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_File_Contract(ByVal ContractID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "DELETE FROM tblFileContract WHERE ContractID=@ContractID"
        With cmd.Parameters
            .AddWithValue("@ContractID", ContractID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function
    'End File Contract

    'File CasePlaintiff
    Public Function select_File_CasePlaintiff_byCaseID(ByVal CaseID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT FileCasePlaintiffID,FileName,FileUpload,UploadDate,UserID FROM tblFileCasePlaintiff WHERE CaseID=N'" & CaseID & "'")
        Return dt
    End Function

    Public Function insert_File_CasePlaintiff(ByVal CaseID As Long, ByVal FileName As String, ByVal FileUpload As Byte(), ByVal UploadDate As Date, ByVal UserID As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblFileCasePlaintiff(CaseID,FileName,FileUpload,UploadDate,UserID) VALUES(@CaseID,@FileName,@FileUpload,@UploadDate,@UserID)"
        With cmd.Parameters
            .AddWithValue("@CaseID", CaseID)
            .AddWithValue("@FileName", FileName)
            If FileName IsNot Nothing Then
                .AddWithValue("@FileUpload", FileUpload)
            Else
                .Add("@FileUpload", SqlDbType.Image).Value = DBNull.Value
            End If
            .AddWithValue("@UploadDate", UploadDate)
            .AddWithValue("@UserID", UserID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_File_CasePlaintiff(ByVal CaseID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "DELETE FROM tblFileCasePlaintiff WHERE CaseID=@CaseID"
        With cmd.Parameters
            .AddWithValue("@CaseID", CaseID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function
    'End File CasePlaintiff

    'File CaseDefendant
    Public Function select_File_CaseDefendant_byCaseID(ByVal CaseID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT FileCaseDefendantID,FileName,FileUpload,UploadDate,UserID FROM tblFileCaseDefendant WHERE CaseID=N'" & CaseID & "'")
        Return dt
    End Function

    Public Function insert_File_CaseDefendant(ByVal CaseID As Long, ByVal FileName As String, ByVal FileUpload As Byte(), ByVal UploadDate As Date, ByVal UserID As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblFileCaseDefendant(CaseID,FileName,FileUpload,UploadDate,UserID) VALUES(@CaseID,@FileName,@FileUpload,@UploadDate,@UserID)"
        With cmd.Parameters
            .AddWithValue("@CaseID", CaseID)
            .AddWithValue("@FileName", FileName)
            If FileName IsNot Nothing Then
                .AddWithValue("@FileUpload", FileUpload)
            Else
                .Add("@FileUpload", SqlDbType.Image).Value = DBNull.Value
            End If
            .AddWithValue("@UploadDate", UploadDate)
            .AddWithValue("@UserID", UserID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_File_CaseDefendant(ByVal CaseID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "DELETE FROM tblFileCaseDefendant WHERE CaseID=@CaseID"
        With cmd.Parameters
            .AddWithValue("@CaseID", CaseID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function
    'End File CaseDefendant

    'File CaseThirdParty
    Public Function select_File_CaseThirdParty_byCaseID(ByVal CaseID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT FileCaseThirdPartyID,FileName,FileUpload,UploadDate,UserID FROM tblFileCaseThirdParty WHERE CaseID=N'" & CaseID & "'")
        Return dt
    End Function

    Public Function insert_File_CaseThirdParty(ByVal CaseID As Long, ByVal FileName As String, ByVal FileUpload As Byte(), ByVal UploadDate As Date, ByVal UserID As String) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblFileCaseThirdParty(CaseID,FileName,FileUpload,UploadDate,UserID) VALUES(@CaseID,@FileName,@FileUpload,@UploadDate,@UserID)"
        With cmd.Parameters
            .AddWithValue("@CaseID", CaseID)
            .AddWithValue("@FileName", FileName)
            If FileName IsNot Nothing Then
                .AddWithValue("@FileUpload", FileUpload)
            Else
                .Add("@FileUpload", SqlDbType.Image).Value = DBNull.Value
            End If
            .AddWithValue("@UploadDate", UploadDate)
            .AddWithValue("@UserID", UserID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_File_CaseThirdParty(ByVal CaseID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "DELETE FROM tblFileCaseThirdParty WHERE CaseID=@CaseID"
        With cmd.Parameters
            .AddWithValue("@CaseID", CaseID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function
    'End File CaseThirdParty

End Class
